﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Kugar.Core.Data.DBCommon;
using System.Data.OleDb;
using Kugar.Core.ExtMethod;

#if Access
namespace Kugar.Core.Data.AccessHelper
{
    public class AccessHelper : DbDatabaseHelperBase
    {
        public AccessHelper(string connString) : base(connString)
        {
        }

        public AccessHelper(DbConnection conn) : base(conn)
        {
        }

        protected override void OpenConnection()
        {
            if (Connection == null || Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken)
            {
                if (ConnectionString.IsEmptyOrWhileSpace())
                {
                    throw new NoNullAllowedException("连接字符串不能为空");
                }

                try
                {
                    if (Connection == null)
                    {
                        Connection = new OleDbConnection();
                    }

                    Connection.ConnectionString = ConnectionString;

                    Connection.Open();

                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        protected override void ExecuteDataTableInternal(DbCommand cmd, DataTable tbl)
        {
            var da = new OleDbDataAdapter((OleDbCommand) cmd);

            try
            {
                da.Fill(tbl);
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();

                da.Dispose();
            }
        }

        protected override void ExecuteDataSetInternal(DbCommand cmd, DataSet ds)
        {
            var da = new OleDbDataAdapter((OleDbCommand)cmd);

            try
            {
                da.Fill(ds);
            }
            finally
            {
                cmd.Transaction = null;
                cmd.Connection = null;
                cmd.Dispose();

                da.Dispose();
            }
        }

        #region "读取数据库结构的函数"

        private const string getSchemaSqlstr = @"select top 1 * from {0}";
        public override DataTable GetDatabaseTableSchema(string srcTableName, SchemaType schemaType)
        {
            if (srcTableName.IsNullOrEmpty())
            {
                throw new NoNullAllowedException("源表名不能为空");
            }

            var sqlstr = string.Format(getSchemaSqlstr, srcTableName);

            var da = new OleDbDataAdapter(sqlstr, (OleDbConnection)Connection);

            var tbl = new DataTable(srcTableName);

            da.FillSchema(tbl, schemaType);

            return tbl;
        }

        public override DataSet GetDatabaseAllTableSchema(SchemaType schemaType)
        {
            var nameList = GetAllTableNames();

            if (nameList.IsEmptyData())
            {
                return null;
            }

            var cmd = (OleDbCommand)Connection.CreateCommand();
            var da = new OleDbDataAdapter(cmd);
            var ds = new DataSet("tableSchema");
            foreach (string tableName in nameList)
            {
                var sqlstr = string.Format(getSchemaSqlstr, tableName);

                cmd.CommandText = sqlstr;

                var tbl = new DataTable(tableName);

                da.FillSchema(tbl, schemaType);

                ds.Tables.Add(tbl);
            }

            return ds;
        }

        public override string[] GetAllTableNames()
        {
            var conn = (OleDbConnection) Connection;

            var tbl = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (tbl.IsEmptyData())
            {
                return null;
            }

            var lst = new List<string>();

            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                var row = tbl.Rows[i];

                lst.Add(row["Table_Name"].ToString());
            }

            return lst.ToArray();
        }

        #endregion

        protected override string BuildPagingSql(PagingArgs args)
        {
            var orderbyStr = CreateOrderSql(args.OrderSql, args.KeyField);

            var pagedSql = "";

            //if (args.PageIndex == 1)//第一页
            //{
            //    pagedSql = string.Format("select top {0} {1} from ( {2} ) order by {3}", args.PageSize, args.ColumnSql, args.TableSql, orderbyStr);
            //}
            //else if (args.PageIndex > pageCount)//超出总页数
            //{
            //    pagedSql = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);
            //}
            //else
            //{
            //    int pageLowerBound = args.PageSize * args.PageIndex;
            //    int pageUpperBound = pageLowerBound - args.PageSize;

            //    //string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);

            //    pagedSql = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);

            //}

            return pagedSql;
        }

        protected override string BuildGetRecordCountSql(PagingArgs args)
        {
            const string sqlstr = @"Select count(*) 
                                                 from 
                                                 (
                                                        {0}
                                                )
                                                ";

            return string.Format(sqlstr, args.TableSql);
        }

        ////排序是否包含了
        private static string CreateOrderSql(string orderSql, string keyField)
        {
            if (string.IsNullOrEmpty(keyField)) throw new ArgumentException("必须设置keyField", keyField);
            string[] keyFields = keyField.Split(',');

            if (string.IsNullOrEmpty(orderSql))
            {
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < keyFields.Length; ++i)
                {
                    if (i != 0) sb.Append(",");
                    sb.AppendFormat("{0} asc", keyFields[i]);
                }
                return sb.ToString();
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(orderSql);
                foreach (var field in keyFields)
                {
                    if (!orderSql.Contains(field))
                    {
                        sb.AppendFormat(",{0} asc", field);
                    }
                }
                return sb.ToString();
            }
        }
    }
}
#endif


